/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package datos;

import java.sql.*;
/**
 *
 * @author diego
 */


public class conectarBBDD {
      
    //public static class conectarBBDD () throws Exception {
    String bd = "ejercicio_login";
    String login = "root";


    String password = "bsvcys82";
    String url = "jdbc:mysql://localhost/"+bd;
   // String url = "jdbc:mysql://localhost:3306/ejercicio_login";
    Connection conn = null;


  /*  public static void main (String args[]) {
        conectar();
    }*/

    public Connection conectar() {
          try {


             Class.forName("com.mysql.jdbc.Driver");

             //conn = DriverManager.getConnection(url,login,password);

             //jdbc:mysql://localhost:3306/profesor

             /**** Realizamos la conexión ****/
             System.out.println("MIERDA GORDA: "+ url);
             
             conn = DriverManager.getConnection( url, login, password );


             if (conn != null) {
                System.out.println("Conexión a base de datos "+url+" ... Ok");
             }
          }
          catch(Exception ex) {
             System.out.println(ex + "\n" + "Hubo un problema al intentar conectarse con la base de datos "+url);
          }
          return conn;
     }
    
    public boolean validarUsuario(String dni, String password) {
        //Establecemos la conexión
        ResultSet rs = null;
        boolean ok = false;
        try {
            Connection conn = conectar();
            Statement st = conn.createStatement();

            String consulta = "select Password from usuarios where DNI = " + "\"" + dni + "\"";
            System.out.println(consulta);
            rs = st.executeQuery (consulta);
            if (rs.next()) {
                if (password.equals(rs.getString(1))) {
                    ok = true;
                }
            }
            conn.close();
        }
        catch (Exception e) {
            System.out.println(e);
        }
        return ok;
    }

    public Object[] obtenerDatosPersonales(String dni) {
        Object[] datos = new Object[10];
        Connection conn = conectar();
        ResultSet rs = null;
        int i = 0;
        try {
            Statement st = conn.createStatement();
            String consulta = "Select Dni, Nombre, Apellidos, Correo, categoria.Descripcion" +
                    " from usuarios, categoria where dni = " + "\"" + dni + "\"" +
                    " and categoria.ID_Categoria = usuarios.Categoria";
            rs = st.executeQuery(consulta);
            while (rs.next()){
                while (rs.getObject(i+1) != null) {
                    datos[i] = rs.getObject(i+1);
                    i++;
                }
            }
        }
        catch (Exception e) {

        }

        return datos;
    }
    public Object[][] obtenerAsignaturas(String dni) {
        Object[][] datos = new Object[2][15];
        Connection conn = conectar();
        ResultSet rs = null;
        int x = 0;
        int i = 0;
        try {
            Statement st = conn.createStatement();
            String consulta = "select asignatura.ID_Asignatura,asignatura.Nombre from usuarios, asignatura, usuariosAsignatura" +
                              " where usuarios.DNI = \""+ dni + "\" and usuarios.DNI = usuariosAsignatura.DNI and" +
                              " usuariosAsignatura.ID_Asignatu = asignatura.ID_Asignatura";
            rs = st.executeQuery(consulta);
            while (rs.next()){
                //Id Asignatura
                    datos[0][i] = rs.getObject(1);

                //Nombre Asignatura
                    datos[1][i] = rs.getObject(2);
                    System.out.println(datos[0][i].toString() + " " + datos[1][i].toString());
                    i++;
            }
        }
        catch (Exception e) {

        }

        return datos;
    }
     public Object[][] obtenerActualizaciones() {
        Object[][] datos = new Object[2][10];
        Connection conn = conectar();
        ResultSet rs = null;
        int x = 0;
        int i = 0;
        try {
            Statement st = conn.createStatement();
            String consulta = "select fecha, descripcion from actualizaciones";
            rs = st.executeQuery(consulta);
            while (rs.next()){
                //Id Asignatura
                    datos[0][i] = rs.getObject(1);

                //Nombre Asignatura
                    datos[1][i] = rs.getObject(2);
                    System.out.println(datos[0][i].toString() + " " + datos[1][i].toString());
                    i++;
            }
        }
        catch (Exception e) {

        }

        return datos;
    }
    public String obtenerNombreAsignatura(int ID_Asignatura) {
        String Asignatura = "";
        Connection conn = conectar();
        ResultSet rs = null;
        int x = 0;
        int i = 0;
        try {
            Statement st = conn.createStatement();
            String consulta = "Select Nombre from asignatura where ID_Asignatura =" + ID_Asignatura;
            rs = st.executeQuery(consulta);
            while (rs.next()) Asignatura = rs.getObject(1).toString();
        }
        catch (Exception e) {

        }

        return Asignatura;
    }
    public Object[][] obtenerNotasEvaluacion(String dni, int evaluacion, int ID_Asignatura) {
        Object[][] datos = new Object[2][10];
        Connection conn = conectar();
        ResultSet rs = null;
        int x = 0;
        int i = 0;
        try {
            Statement st = conn.createStatement();
            String consulta = "select Fecha, Nota from notas where ID_Asignatura = " + ID_Asignatura +
                    " and Evaluacion = " + evaluacion + " and DNI = \"" + dni + "\" order by fecha";
            rs = st.executeQuery(consulta);
            while (rs.next()){
                //Fecha
                    datos[0][i] = rs.getObject(1);

                //Nota
                    datos[1][i] = rs.getObject(2);
                    System.out.println(datos[0][i].toString() + " " + datos[1][i].toString());
                    i++;
            }
        }
        catch (Exception e) {

        }

        return datos;
    }
}